﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CMS.Entity;

namespace CMS.DAL
{
    public class PartnerDAL
    {
        /// <summary>
        /// 根据页码取对应的伙伴记录，按名称排序
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        public List<PartnerEnty> GetLst(int p, string title,string types, out int count)
        {
            StringBuilder sbSql = new StringBuilder();
            Tools tool = new Tools();
            tool.AddSqlParameter("pname", System.Data.SqlDbType.VarChar, "%" + title + "%");
            if (types != "")
            {
                tool.AddSqlParameter("tpyeid", System.Data.SqlDbType.VarChar, types);
                sbSql.Append("select count(*) from partners where pname like @pname and padr=@tpyeid");
            }
            else
            {
                sbSql.Append("select count(*) from partners where pname like @pname ");
            }
            count = int.Parse(SqlHelper.ExecuteScalar(DBConn.CMS, System.Data.CommandType.Text, sbSql.ToString(), tool.GetSqlParameters()).ToString());

            sbSql = new StringBuilder();
            int len = 10;
            if (count - (p - 1) * 10 < 10)
                len = count - (p - 1) * 10;

            sbSql.AppendFormat(" select top {0} * from", len);
            sbSql.AppendFormat("(select top {0} * from partners where 1=1 and pname like @pname ", p * 10);
            if (types != "")
                sbSql.Append(" and padr=@tpyeid" );
            sbSql.AppendFormat(" order by sortnum desc) as a order by sortnum asc ");

            return SqlHelper.GetDataByColumnsLst<PartnerEnty>(DBConn.CMS, sbSql.ToString(), tool.GetSqlParameters());
        }

        public int AddPartner(PartnerEnty pt)
        {
            string sql = "INSERT INTO [Partners] ([Pname] ,[Ppicture] ,[Padr] ,[Pemail] ,[Pphone] ,[Pdes] ,[Purl] ,[CreateTime] ,[Status],[SortNum]) VALUES (@Pname ,@Ppicture ,@Padr ,@Pemail ,@Pphone ,@Pdes ,@Purl ,@CreateTime ,@Status,@SortNum)";
            var sps = SqlHelper.ObjectToSqlParameters(pt);
            return SqlHelper.ExecuteNonQuery(DBConn.CMS, System.Data.CommandType.Text, sql, sps);
        }

        public int UpdatePartner(PartnerEnty pt)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("UPDATE [Partners]");
            sb.AppendLine("   SET [Pname] = @Pname");
            sb.AppendLine("      ,[Ppicture] = @Ppicture");
            sb.AppendLine("      ,[Padr] = @Padr");
            sb.AppendLine("      ,[Pemail] = @Pemail");
            sb.AppendLine("      ,[Pphone] = @Pphone");
            sb.AppendLine("      ,[Pdes] = @Pdes");
            sb.AppendLine("      ,[Purl] = @Purl");
            sb.AppendLine("      ,[CreateTime] = @CreateTime");
            sb.AppendLine("      ,[Status] = @Status");
            sb.AppendLine("      ,[SortNum] = @SortNum");
            sb.AppendLine(" WHERE [Pid]=@Pid");
            var sps = SqlHelper.ObjectToSqlParameters(pt);
           return SqlHelper.ExecuteNonQuery(DBConn.CMS, System.Data.CommandType.Text,sb.ToString(),sps);
        }

        public PartnerEnty GetOne(int pid)
        {
            Tools tool=new Tools();
            tool.AddSqlParameter("Pid", System.Data.SqlDbType.Int,pid);
            string sql="select * from partners where pid=@pid";
            return SqlHelper.GetDataByColumns<PartnerEnty>(DBConn.CMS, sql, tool.GetSqlParameters());
        }

        public int DeletePartnerById(int pid)
        {
            string sql = "delete from partners where pid=@pid";
            Tools tool = new Tools();
            tool.AddSqlParameter("pid", System.Data.SqlDbType.Int, pid);
            int res = SqlHelper.ExecuteNonQuery(DBConn.CMS, System.Data.CommandType.Text, sql, tool.GetSqlParameters()) ;
            return res;
        }
    }
}
